package main

import (
	"strconv"
	"github.com/xuri/excelize"
	"fmt"
	"whoscored/util/mysql"
	"strings"
	"time"
	"math"
	"whoscored/qiutan"
)
//联赛上个赛季的场均进球
func leagueSeasonGoals(currentSeason string,regionName string,tournamentName string) interface{} {

	sql:="select avg(homeScore+awayScore) as goals from `match` where season=? and regionName=? and tournamentName=?"
	seasonTarget:=""
	if regionName=="Argentina"{

		if currentSeason=="2016/2017"{
			seasonTarget="2016"
		}

	}else{
		if strings.Contains(currentSeason,"/"){
			slice:=strings.Split(currentSeason,"/")
			a,_:=strconv.ParseFloat(slice[0],64)
			b,_:=strconv.ParseFloat(slice[1],64)
			seasonTarget=strconv.FormatFloat(a-1,'f',-1,64)+"/"+strconv.FormatFloat(b-1,'f',-1,64)

		}else{
			a,_:=strconv.ParseFloat(currentSeason,64)
			seasonTarget=strconv.FormatFloat(a-1,'f',-1,64)
		}
	}
	if regionName=="Germany"{
		tournamentName="1. Bundesliga"
	}
	if regionName=="Argentina"{
		tournamentName="Primera Division"
	}
	if regionName=="Portugal"{
		tournamentName="Primeira Liga"
	}
	if regionName=="Brazil"{
		tournamentName="Serie A"
	}

	result:=mysql.FindFirst(sql,seasonTarget,regionName,tournamentName)
	if result["goals"]==nil{
		return -9999
	}
	return result["goals"]

}
//球队上个赛季的均进球
func lastSeasonRating2(team interface{},t int) []interface{} {
	sql:="select * from team_season where type=? and teamId=? and season=? and regionCode=? and tournamentName=?"
	data:=team.(map[string]interface{})
	season:=data["season"].(string)
	seasonTarget:=""
	if data["regionName"]=="Argentina"{

		if season=="2016/2017"{
			seasonTarget="2016"
		}

	}else{
		if strings.Contains(season,"/"){
			slice:=strings.Split(season,"/")
			a,_:=strconv.ParseFloat(slice[0],64)
			b,_:=strconv.ParseFloat(slice[1],64)
			seasonTarget=strconv.FormatFloat(a-1,'f',-1,64)+"/"+strconv.FormatFloat(b-1,'f',-1,64)

		}else{
			a,_:=strconv.ParseFloat(season,64)
			seasonTarget=strconv.FormatFloat(a-1,'f',-1,64)
		}
	}


	result:=mysql.FindFirst(sql,t,data["teamId"],seasonTarget,data["regionCode"],data["tournamentName"])

	if result["rating"]==nil{
		//取联赛本赛季的平均分
		sql2:="select avg(rating) as rating,avg(goals) as goals " +
			//"avg(shotsOnTarget) as shotsOnTarget,avg(shots) as shots," +
			//"avg(dribblesWon) as dribblesWon,avg(throwins) as throwins," +
			//"avg(offsides) as offsides,avg(tackles) as tackles," +
			//"avg(shotsBlocked) as shotsBlocked " +
			"from team_season where type=? and season=? and regionCode=? and tournamentName=?"
		result2:=mysql.FindFirst(sql2,t,seasonTarget,data["regionCode"],data["tournamentName"])
		if result2["rating"]==nil{
			return []interface{}{float64(5),float64(-9999)}
		}

		//shotsOnTarget,_:=result2["shotsOnTarget"].(float64)
		//shots,_:=result2["shots"].(float64)
		return []interface{}{
			result2["rating"],result2["goals"],
		}
	}

	return []interface{}{
		result["rating"],result["goals"],
		//result["dribblesWon"],
		//result["throwins"],
		//result["offsides"],
		//result["tackles"],
		//result["shotsBlocked"],

	}
}

//本赛季已完成10场比赛
func isTen2(team interface{},t int) bool {
	sql:="select count(*) as count from team_match where teamId=? and startTimeUtc<? " +
		"and season=? and regionCode=? and tournamentName=?"
	if t==1{
		sql+=" and isHome=1"
	}
	if t==2{
		sql+=" and isHome=0"
	}
	data:=team.(map[string]interface{})
	result:=mysql.FindFirst(sql,data["teamId"],data["startTimeUtc"],data["season"],data["regionCode"],data["tournamentName"])

	if result["count"].(int64)>=10{
		return true
	}
	return false
}
//本赛季评分
func seasonRating2(team interface{},t int) []interface{} {
	sql:="select avg(rating) as rating,avg(goals) as goals " +

		"from team_match where teamId=? and startTimeUtc<? " +
		"and season=? and regionCode=? and tournamentName=?"
	if t==1{
		sql+=" and isHome=1"
	}
	if t==2{
		sql+=" and isHome=0"
	}
	data:=team.(map[string]interface{})
	result:=mysql.FindFirst(sql,data["teamId"],data["startTimeUtc"],data["season"],data["regionCode"],data["tournamentName"])

	if result["rating"]==nil{
		return []interface{}{float64(5),float64(-9999)}
	}
	goals,_:=strconv.ParseFloat(result["goals"].(string),64)

	return []interface{}{
		result["rating"],goals,
	}
}
//球队评分
func teamRating2(team interface{},t int) ([]interface{}) {

	if isTen2(team,t){
		return seasonRating2(team,t)
	}else{
		return lastSeasonRating2(team,t)
	}

}
//最近6场进球情况
func last6Match2(homeTeam bool,team interface{},ratingHome float64,ratingAway float64) (bool,[]float64) {

	home:=team.(map[string]interface{})

	/*sql:="select * from (" +
		"select * from `match` t1 where t1.homeId=? and t1.startTimeUtc<? and t1.regionCode=? and t1.tournamentName=? " +
		"union all " +
		"select * from `match` t2 where t2.awayId=? and t2.startTimeUtc<? and t2.regionCode=? and t2.tournamentName=?) " +
		"as t order by t.startTimeUtc desc limit 6"*/
	sql:="select tm.*,m.homeId,m.awayId from team_match tm " +
		"left join `match` m on m.matchId=tm.matchId " +
		"where tm.teamId=? and tm.startTimeUtc<? " +
		"and tm.regionCode=? and tm.tournamentName=?"
	if homeTeam{
		sql+=" and tm.isHome=1"
	}else{
		sql+=" and tm.isHome=0"
	}
	sql+=" order by tm.startTimeUtc desc limit 6"
	result:=mysql.FindAll(sql,
		home["teamId"],home["startTimeUtc"],home["regionCode"],home["tournamentName"],
		//home["teamId"],home["startTimeUtc"],home["regionCode"],home["tournamentName"],
	)

	teamId,_:=strconv.ParseInt(home["teamId"].(string),10,64)
	ff:=make([]float64,0)
	i:=0
	totalScore:=float64(0)
	lossScore:=float64(0)
	//totalDiff:=float64(0)
	for _,v:=range result {
		isHome:=false
		betId,_:=strconv.ParseInt(v["homeId"].(string),10,64)

		if betId==teamId{
			isHome=true
			betId,_=strconv.ParseInt(v["awayId"].(string),10,64)
		}
		/*d:=map[string]interface{}{
			"teamId":betId,
			"startTimeUtc":v["startTimeUtc"],
			"season":v["season"],
			"regionCode":v["regionCode"],
			"tournamentName":v["tournamentName"],
		}*/

		homeScore,_:=strconv.ParseFloat(v["homeScore"].(string),64)
		awayScore,_:=strconv.ParseFloat(v["awayScore"].(string),64)
		if(isHome){
			//betRating:=teamRating2(d,2)[0].(float64)
			//ratingDiff:=ratingHome-betRating
			//totalDiff+=ratingDiff

			totalScore+=homeScore
			lossScore+=awayScore
		}else{
			//betRating:=teamRating2(d,1)[0].(float64)
			//ratingDiff:=ratingAway-betRating
			//totalDiff+=ratingDiff

			totalScore+=awayScore
			lossScore+=homeScore
		}
		if i==0{
			ff=append(ff,totalScore)
			ff=append(ff,lossScore)
			//ff=append(ff,totalDiff)
		}
		if i==2{
			ff=append(ff,totalScore/float64(3))
			//ff=append(ff,lossScore/float64(3))
			//ff=append(ff,totalDiff/float64(3))
		}
		if i==5{
			ff=append(ff,totalScore/float64(6))
			//ff=append(ff,lossScore/float64(6))
			//ff=append(ff,totalDiff/float64(6))
		}
		i++

	}
	size:=len(ff)

	if size<4{
		return false,nil
	}
	return true,ff
}
//上六场对战
func last6Fight2(team1 interface{},team2 interface{}) (bool,[]float64) {
	sql:="select t1.*,t2.rating as awayRating from team_match t1,team_match t2 " +
		"where t1.matchId=t2.matchId and t1.teamId=? and t1.startTimeUtc<? and t1.regionCode=? " +
		"and t1.tournamentName=? " +
		"and t2.teamId=? " +
		"order by t1.startTimeUtc desc"

	home:=team1.(map[string]interface{})
	away:=team2.(map[string]interface{})
	result:=mysql.FindAll(sql,home["teamId"],home["startTimeUtc"],home["regionCode"],home["tournamentName"],away["teamId"])
	ff:=make([]float64,0)
	totalScore:=float64(0)
	i:=float64(0)
	for _,v:=range result {
		homeScore,_:=strconv.ParseFloat(v["homeScore"].(string),64)
		awayScore,_:=strconv.ParseFloat(v["awayScore"].(string),64)
		totalScore+=homeScore+awayScore
		if i==0{
			ff=append(ff,totalScore)
		}
		if i==1{
			ff=append(ff,totalScore/float64(2))
		}
		/*if i==2{
			ff=append(ff,totalScore/float64(3))
		}*/
		i++
	}
	if len(ff)<2{
		return false,nil
	}
	ff=append(ff,totalScore/(i+1))

	return true,ff
}
//球探比赛id
func qtMatch2(team1 interface{},team2 interface{}) (bool,[]interface{}) {
	sql1:="select * from qt_team where wid=?"
	sql2:="select * from qt_match where home=? and away=? and startAt>=? and startAt<=?"
	home:=team1.(map[string]interface{})
	away:=team2.(map[string]interface{})
	startTimeUtc:=home["startTimeUtc"].(string)

	time1,_:=time.Parse("2006-01-02 15:04:05",startTimeUtc)
	//两个网站的开始时间可能相差10几分钟
	t:=time1.Add(time.Hour*8)
	t2:=t.Add(time.Minute*30)
	t1:=t.Add(time.Minute*-30)
	date:=t.Format("2006-01-02 15:04:05")
	startAt:=t1.Format("2006-01-02 15:04:05")
	endAt:=t2.Format("2006-01-02 15:04:05")

	qtHomeTeam:=mysql.FindFirst(sql1,home["teamId"])
	homeName:=qtHomeTeam["name"].(string)
	qtAwayTeam:=mysql.FindFirst(sql1,away["teamId"])
	awayName:=qtAwayTeam["name"].(string)

	match:=mysql.FindFirst(sql2,homeName,awayName,startAt,endAt)
	if match==nil{
		return false,nil
	}
	fmt.Println(homeName,awayName,startAt,endAt,match)
	return true,[]interface{}{
		date,match["id"].(int64),match["league"].(string),homeName,awayName,match["weather"],
	}
}

/**
疲劳度计算：
1、平均比赛频率小于x天
2、连续x次以上客场作战
3、近x场是否有保级争冠等重要比赛，且时间小于x天
 */
func tired(team interface{},startAt string) []float64 {
	f1:=float64(0)
	f2:=float64(0)
	data:=team.(map[string]interface{})
	//近3场比赛
	sql:="select tournamentName,homeId,awayId,startTimeUtc from `match` " +
		"where (homeId=? or awayId=?) and startTimeUtc<? " +
		"order by startTimeUtc desc limit 3"
	matchs:=mysql.FindAll(sql,data["teamId"],data["teamId"],startAt)
	//比赛频率
	timeFlag,_:=time.Parse("2006-01-02",strings.Split(startAt," ")[0])
	totalDay:=float64(0)
	i:=0
	awayFlag:=0
	for _,v:=range matchs{

		time1,_:=time.Parse("2006-01-02",strings.Split(v["startTimeUtc"].(string)," ")[0])

		totalDay+=timeFlag.Sub(time1).Hours()/24
		timeFlag=time1
		//是否连续两次客场作战
		awayId,_:=strconv.ParseFloat(v["awayId"].(string),64)
		teamId,_:=strconv.ParseFloat(data["teamId"].(string),64)

		if awayId==teamId{
			if awayFlag>=1{
				f2=float64(1)
			}
			awayFlag++
		}else{
			awayFlag=0
		}
		i++
	}

	if totalDay/3<3{
		f1=1
	}
	return []float64{f1,f2}
}
//球队球员评分
func playerRating2(playerId interface{},position string,startTimeUtc interface{},isHome bool) (float64,float64) {
	sql:="select avg(rating) as rating,avg(goals) as goals from " +
		"(select rating,goals from player_match where playerId=? and position=? and startTimeUtc<? and rating>0";
	if isHome{
		sql+=" and isHome=1"
	}else{
		sql+=" and isHome=0"
	}
	sql+=" order by startTimeUtc desc limit 6)" +
		" as t"
	result:=mysql.FindFirst(sql,playerId,position,startTimeUtc)

	if result["rating"]==nil{
		return float64(5),float64(0)
	}
	goals,_:=strconv.ParseFloat(result["goals"].(string),64)
	return result["rating"].(float64),goals
}
/**
球员分析
GK：守门员
DL：左后卫
DC：中后卫
DR：右后卫
AML：左边锋
AMC：中边锋
AMR：右边锋
Sub：替补
ML：左前卫
MC：中前卫
MR：右前卫
FW：前锋
FWL：左前锋
FWR：右前锋
DML：左后腰
DMC：中场后腰。后腰是位置较靠后的中场，是球队中场与防线之间的重要纽带
DMR：右后腰
 */
func playerStat2(team interface{},isHome bool) []float64 {
	data:=team.(map[string]interface{})
	sql:="select playerId,position,startTimeUtc from player_match where matchId=? and teamId=? and isSub=0"
	result:=mysql.FindAll(sql,data["matchId"],data["teamId"])
	ratingMap:=map[string]float64{
		"GK":float64(0),
		"D":float64(0),
		"AM":float64(0),
		"M":float64(0),
		"FW":float64(0),
		"DM":float64(0),
	}

	countMap:=map[string]float64{
		"GK":float64(1),
		"D":float64(0),
		"AM":float64(0),
		"M":float64(0),
		"FW":float64(0),
		"DM":float64(0),
	}
	totalGoals:=float64(0)
	for _,v:=range result{
		pos:=v["position"].(string)
		rating,goals:=playerRating2(v["playerId"],pos,v["startTimeUtc"],isHome)
		totalGoals+=goals

		if pos=="GK"{
			ratingMap["GK"]=rating
		}
		if pos=="DL"||pos=="DC"||pos=="DR"{
			ratingMap["D"]=ratingMap["D"]+rating
			countMap["D"]=countMap["D"]+1
		}
		if pos=="AML"||pos=="AMC"||pos=="AMR"{
			ratingMap["AM"]=ratingMap["AM"]+rating
			countMap["AM"]=countMap["AM"]+1
		}
		if pos=="ML"||pos=="MC"||pos=="MR"{
			ratingMap["M"]=ratingMap["M"]+rating
			countMap["M"]=countMap["M"]+1
		}
		if pos=="FW"||pos=="FWL"||pos=="FWR"{
			ratingMap["FW"]=ratingMap["FW"]+rating
			countMap["FW"]=countMap["FW"]+1
		}
		if pos=="DML"||pos=="DMC"||pos=="DMR"{
			ratingMap["DM"]=ratingMap["DM"]+rating
			countMap["DM"]=countMap["DM"]+1
		}
	}
	if countMap["D"]>0{
		ratingMap["D"]=ratingMap["D"]/countMap["D"]
	}
	if countMap["AM"]>0{
		ratingMap["AM"]=ratingMap["AM"]/countMap["AM"]
	}
	if countMap["M"]>0{
		ratingMap["M"]=ratingMap["M"]/countMap["M"]
	}
	if countMap["FW"]>0{
		ratingMap["FW"]=ratingMap["FW"]/countMap["FW"]
	}
	if countMap["DM"]>0{
		ratingMap["DM"]=ratingMap["DM"]/countMap["DM"]
	}
	fmt.Println(totalGoals,countMap,ratingMap)
	//rd:=(ratingMap["D"]+ratingMap["DM"])/(countMap["D"]+countMap["DM"])
	//ra:=(ratingMap["AM"]+ratingMap["M"]+ratingMap["FW"])/(countMap["AM"]+countMap["M"]+countMap["FW"])
	rating:=(ratingMap["D"]+ratingMap["DM"]+ratingMap["AM"]+ratingMap["M"]+ratingMap["FW"])/10
	return []float64{
		//countMap["GK"],countMap["D"],countMap["AM"],countMap["M"],countMap["FW"],countMap["DM"],
		//ratingMap["GK"],ratingMap["D"],ratingMap["AM"],ratingMap["M"],
		//ratingMap["FW"],ratingMap["DM"],
		totalGoals,
		rating,
		//ratingMap["GK"],
		countMap["D"]/10,
		countMap["DM"]/10,
		countMap["AM"]/10,
		countMap["M"]/10,
		countMap["FW"]/10,
	}
}
/**

gb-eng 51
tr 57.8
es 48.6
it 55.9
de 50.2
ar 38.1 数据有问题
git  59.7
ru 48.3
br 41.4
no 54.9
fr 51.9
pt 53.6
pt 55.1
 */
func overunder(fn func([]interface{},int)) {
	sql := "select * from `match` where " +
		"regionCode='fr' " +
		/*"(regionCode='tr' or regionCode='es' " +
		"or regionCode='it' or regionCode='gb-eng' " +
		"or regionCode='de' or regionCode='ar' " +
		"or regionCode='nl' or regionCode='ru' " +
		"or regionCode='br' or regionCode='no' " +
		"or regionCode='fr' or regionCode='pt' " +
		"or regionCode='se') " +*/
		"and tournamentName!='Championship' and tournamentName!='Bundesliga II' " +
		"and detailed=1 " +
		"and startTimeUtc<'2017-12-31 16:00:00' and startTimeUtc>'2017-01-01 00:00:00' " +
		"order by startTimeUtc"
	sql2 := "select * from team_match where matchId=? order by isHome"
	matchs:=mysql.FindAll(sql)
	i:=0
	for _,v:=range matchs {

		data := mysql.FindAll(sql2, v["matchId"])
		if len(data) == 0 {
			continue
		}
		home := data[1]
		away := data[0]

		b,mm:=qtMatch2(home,away)
		if !b{
			continue
		}
		qiutan.OverunderFormat(
			mm[0].(string),
			mm[1].(int64),
			mm[2].(string),
			mm[3].(string),
			mm[4].(string),
			func(data [][]float64) {

			//homeTired:=tired(home,v["startTimeUtc"].(string))
			//awayTired:=tired(away,v["startTimeUtc"].(string))
			//联赛均进球
			avgGoals:=leagueSeasonGoals(v["season"].(string),v["regionName"].(string),v["tournamentName"].(string))

			//主队评分
			homeAll:=teamRating2(home,0)
			//客队评分
			awayAll:=teamRating2(away,0)

			//主队主场，客队客场
			homeHome:=teamRating2(home,1)
			homeAway:=teamRating2(home,2)
			awayHome:=teamRating2(away,1)
			awayAway:=teamRating2(away,2)

			//滑动窗口
			b1,h:=last6Match2(true,home,homeHome[0].(float64),homeAway[0].(float64))
			if !b1{
				return
			}

			b2,a:=last6Match2(false,away,awayHome[0].(float64),awayAway[0].(float64))
			if !b2{
				return
			}

			b3,f:=last6Fight2(home,away)
			if !b3{
				return
			}

			//球员分析
			homePlayerState:=playerStat2(home,true)
			awayPlayerState:=playerStat2(away,false)

			w:=0
			s:=0
			homeScore,_:=strconv.ParseFloat(v["homeScore"].(string),64)
			awayScore,_:=strconv.ParseFloat(v["awayScore"].(string),64)
			//大小球初盘
			pk:=data[1][0]
			if homeScore+awayScore-pk>=0{
				s=1
			}
			if homeScore-awayScore==0{
				w=1
			}
			if homeScore-awayScore<0{
				w=2
			}

			slice := []interface{}{
				v["matchId"],
				w,
				s,
				homeScore,awayScore,
				v["regionCode"],
				mm[5],
				avgGoals,
				homePlayerState[0],awayPlayerState[0],
				homePlayerState[1],awayPlayerState[1],
				homePlayerState[2],awayPlayerState[2],
				homePlayerState[3],awayPlayerState[3],
				homePlayerState[4],awayPlayerState[4],
				homePlayerState[5],awayPlayerState[5],
				math.Abs(homeAll[0].(float64)-awayAll[0].(float64)),
				homeAll[1].(float64)+awayAll[1].(float64),
				math.Abs(homeHome[0].(float64)-awayAway[0].(float64)),
				homeHome[1].(float64)+awayAway[1].(float64),
			}

			f2:=[][]float64{data[0],data[1],h,a,f}

			for _, v := range f2 {
				for _, vv := range v {
					slice = append(slice, vv)
				}

			}
			i++
			fmt.Println(i,slice)
			fn(slice,i)
		})

	}
}
/**
1、疲劳度统计对大小球几乎没有影响
2、两队的主场评分和客场评分差距越大，大球率越高
3、两队主场均进球+客场均进球，值越大，大球率越高
4、让球数越大，大球率越高
5、终盘与初盘的差值越小，大球率越高。（升盘意味着大球率提高）
6、最近一场进球数的和，越大，大球率越高。失球数和越大，大球率越小
7、近3/6场进球数和越大，大球率越高。但近3/6场的失球数对大球率对影响不规则（可以去掉）。近6场的比赛影响很小（可以去掉）
8、对战进球数和越大，大球率越高。考虑只看所有对战的均进球数
9、主客两队均进球和越大，大球率越大
10、主客两队评分差距越大，大球率越大
11、主客球员的均进球和，不影响赢盘机率。但主队球员的进球数影响赢盘率，客队球员的进球数，不影响硬盘率
12、主客守门员评分对结果无影响
13、主防守评分与客进攻差值越小，大球率越高。但客队防守与主队进攻的差值，是一个波峰抛物线


 */
func main()  {
	e:=[]string{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}
	xlsx := excelize.NewFile()

	categories := []string{

	}
	for k,v:=range categories{
		len:=int(k/26)
		s:=""
		if len>0{
			s+=e[len-1]+e[k%26]
		}else{
			s+=e[k%26]
		}

		xlsx.SetCellValue("Sheet1",s+"1", v)
	}
	overunder(func(s []interface{}, i int) {
		for k,v:=range s{
			len:=int(k/26)
			s:=""
			if len>0{
				s+=e[len-1]+e[k%26]
			}else{
				s+=e[k%26]
			}
			xlsx.SetCellValue("Sheet1",s+strconv.Itoa(i+1), v)
		}
	})
	err := xlsx.SaveAs("./Workbook.xlsx")
	if err != nil {
		fmt.Println(err)
	}
}
